import pymysql
from lib.func import get_config

"""
连接池
"""


class Conn(object):

    init_flag = False

    def __init__(self):
        if not Conn.init_flag:
            conf = get_config('DB')
            self.__db_host = conf['host']
            self.__db_user = conf['user']
            self.__db_password = conf['password']
            self.__db_name = conf['database']
            self.__db_port = conf.get('port', 3306)
            self._conn = None
            self._cursor = None
            Conn.init_flag = True

    def __new__(cls, *args, **kw):
        """
        启用单例模式
        :param args:
        :param kw:
        :return:
        """
        if not hasattr(cls, '_instance'):
            cls._instance = object.__new__(cls)
        return cls._instance

    def connect(self):
        """
        启动连接
        :return:
        """
        if self._conn and self._cursor:
            return
        self._conn = pymysql.connect(
            host=self.__db_host,
            user=self.__db_user,
            password=self.__db_password,
            port=int(self.__db_port),
            db=self.__db_name
        )
        # 使用 cursor() 方法创建一个游标对象 cursor
        self._cursor = self._conn.cursor()

    def __del__(self):
        self.connect_close()

    def connect_close(self):
        """
        关闭连接
        :return:
        """
        self._cursor.close()
        self._conn.close()

    def fetch_all(self, sql, args=None):
        """
        批量查询
        :param sql:
        :param args:
        :return:
        """
        self.connect()
        self._cursor.execute(sql, args)
        record_list = self._cursor.fetchall()
        if not record_list:
            return record_list
        description = self._cursor.description
        fields = []
        for desc in description:
            fields.append(desc[0])
        ret = []
        for row in record_list:
            row_dict = {}
            for field, val in zip(fields, row):
                row_dict[field] = val
            ret.append(row_dict)
        return ret

    def fetch_one(self, sql, args=None):
        """
        查询单条数据
        :param sql:
        :param args:
        :return:
        """
        self.connect()
        self._cursor.execute(sql, args)
        record = self._cursor.fetchone()
        if not record:
            return record
        description = self._cursor.description
        fields = []
        for desc in description:
            fields.append(desc[0])
        ret = {}
        for field, val in zip(fields, record):
            ret[field] = val
        return ret

    def insert(self, sql, args=None):
        """
        插入数据
        :param sql:
        :param args:
        :return:
        """
        self.connect()
        row = self._cursor.execute(sql, args)
        self._conn.commit()
        return row

    def insert_dict(self, table, field_dict):
        fields = "%s%s%s" % ('`', '`,`'.join(field_dict.keys()), '`')
        sql = 'INSERT INTO %s(' % table + fields + ') VALUES '
        sql += '(' + ('%s,' * len(field_dict)).rstrip(',') + ')'
        self.connect()
        self._cursor.execute(sql, list(field_dict.values()))
        ret = self._conn.insert_id()
        self._conn.commit()
        return ret

    def update_dict(self, table, field_dict, where):
        sql = 'UPDATE %s SET ' % table + ','.join(['`'+field+'`' + '=%s' for field in field_dict])
        sql += ' WHERE ' + ' and '.join([field + '=%s' for field in where])
        self.connect()
        row = self._cursor.execute(sql, list(field_dict.values()) + list(where.values()))
        self._conn.commit()
        return row

    def fetch_by_key(self, sql, key_field, val_filed=None):
        data = self.fetch_all(sql)
        if not data:
            return None
        ret = {}
        for row in data:
            ret[row[key_field]] = row[val_filed] if val_filed else row
        return ret

